package com.zaofan.dao;

import com.zaofan.bean.Product;
import com.zaofan.bean.Shop;
import com.zaofan.dao.base.DaoSupportImpl;
import com.zaofan.utils.C3p0DataSourceUtils;
import com.zaofan.utils.SqlBuilderUtils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

public class ProductDao extends DaoSupportImpl<Product> {

	public void setProductState(Product product) {
		try {
			String sql = "update " + Product.class.getSimpleName().toLowerCase() + " set state = " + product.getState()
					+ " where id = " + product.getId();
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			runner.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Integer addProduct(Product product) {
		Connection conn = C3p0DataSourceUtils.getConnection();
		int id = 0;
		try {
			String sql = SqlBuilderUtils.builderInsertSql(Product.class, product);
			System.err.println(sql);
			QueryRunner runner = new QueryRunner();
			runner.update(conn, sql);
			// 获取新增记录的自增主键
			try {
				Long l = (Long) runner.query(conn, "SELECT LAST_INSERT_ID()", new ScalarHandler<Long>());
				id = l.intValue();
			} catch (Exception e) {
				BigInteger l = (BigInteger) runner.query(conn, "SELECT LAST_INSERT_ID()",
						new ScalarHandler<BigInteger>());
				id = l.intValue();
			}
		} catch (SQLException e) {
			id = 0;
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}

	public void updateDateSellCount() {
		try {
			String sql = "update " + Product.class.getSimpleName().toLowerCase() + " set dateCount = 0";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			runner.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Product> getWeekProducts(Shop shop, int sort_b, int sort_e) {
		try {
			String sql = "select a.* from product a left join product b on a.categoryId = b.categoryId and a.sort > b.sort and b.state=0"
					+ " where a.state= 0 and a.shopId = " + shop.getId()
					+ " and a.categoryId in (select id from shopcategory where sort >= " + sort_b + " and sort <= "
					+ sort_e + ")" + " group by a.categoryId  having count(b.id) < 2 order by a.categoryId asc;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	/**
	 * 查找本周热卖新品
     */
	public List<Product> findHotProduct(String cityId,int page) {
		try {
			int start = page*10;
			String sql = "select * from product p where p.state = 0 " +
					"and p.shopId != 20 and p.shopId in (select id from shop as s " +
					"where p.id!=799 and s.state = 0 and s.cityId = " + cityId +") " +
					"ORDER BY p.dateCount desc,p.sellcount desc limit " + start + ",10;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	/**
	 * 查找本周人气新品
	 * @param cityId
	 * @return
     */
	public List<Product> findNewProduct(String cityId) {
		try {
			String sql = "select p.* from product as p " +
					"left join shop as s on s.id=p.shopId " +
					"where p.id != 799 and p.state = 0 and s.state = 0 and s.cityId = " + cityId + " and DATEDIFF(CURTIME(),p.addTime) < 28 " +
					"order by p.addTime desc limit 10;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}


    public List<Product> findAllHotProduct(String cityId,String begin,String end) {
		try {
			String sql = "select * from product p where p.state = 0 " +
					"and p.shopId in (select id from shop as s " +
					"where p.id!=799 and s.state = 0 and s.cityId = " + cityId +") " +
					"ORDER BY p.dateCount desc,p.sellcount desc limit " + begin + "," + end+";";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
    }
}
